package multi;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.util.Calendar;
import java.util.Random;

import javax.sql.*;

public class SpatialMultiTestPG implements Runnable {

	public void run() {
		System.out.println("Started: " + Thread.currentThread().getName());
		
		findGeoLocationsDirectQuery();
		
		System.out.println("Done: " + Thread.currentThread().getName());
	} 

	
	public void findGeoLocationsDirectQuery() {
		System.out.println("Using Direct Query");
		Connection con = null;
		try {

			con = Constants.getConnection();
			
			//Statement stmt = con.createStatement();
			String query="";
			double lat=Constants.LAT;
			double lon=Constants.LON;
			double radius=Constants.RADIUS_DEGREE_UNITS;
			
			query="SELECT id, latitude, longitude, ST_DISTANCE(location, 'POINT(" + lat + " " + lon + ")') as distance FROM geocode WHERE ST_DWithin(location, 'POINT(" + lat + " " + lon + ")', " + radius + ")";
			//IMPORTANT: 
			//If : geography suffix is used against POINT in SQL - then the order should be - lon lat and not - lat lon.
			//And the data too should have been inserted as lon lat for geocode location column. Geography suffix uses distances in meters.
			//If geography suffix is not used - then we can simply use - lat lon format.
			
			System.out.println("Query to use: " + query);
			
			PreparedStatement pStmt = con.prepareStatement(query);
			ResultSet rs;
			for (int i = 0; i < Constants.NO_OF_LOOPS_PER_THREAD; i++) {
				long startTime = Calendar.getInstance().getTimeInMillis();
				 rs = pStmt.executeQuery();
				 long timeTaken = Calendar.getInstance().getTimeInMillis()
					- startTime;
				 Constants.addTotalTime(timeTaken);
				 System.out.println(Thread.currentThread().getName()
					+ ": Time Taken: " + timeTaken + " milliseconds");
			
				int rows = 0;
				while (rs.next()) {
					rows++;
					
					System.out.println ("RS: Id: " + rs.getInt("id") +
					" Lat: " + rs.getDouble("latitude") + " Lon: " +
					rs.getDouble("longitude") + " Dist: " +
					rs.getDouble("distance"));
					
				}
				System.out.println("Total Rows: " + rows);
			}
		
		} // end try

		catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Constants.closeConnection(con);
		}
	}

} // end class
